package be.ac.ua.ecampus.uacmat;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.HashSet;
import java.util.Vector;

import be.ac.ua.ecampus.uacmat.entity.*;

public class QueryHandler {
	Connection fConnection;
	Connection fOldConnection = null;

	public QueryHandler(Connection connection) {
		fConnection = connection;
	}

	public QueryHandler() {
		fConnection = this.connect();
	}

	protected void finalize() throws Throwable {
		fConnection.close();
		if(fOldConnection != null) {
			fOldConnection.close();
		}
		super.finalize();
	} 

	public static Connection connect(String url, String driver) {
		Connection connection = null;
		try {
			Class.forName(driver);
			connection = DriverManager.getConnection(url);
		} catch (ClassNotFoundException e) {
			System.out.println("[CMAT ERROR] - [QUERYHANDLER] - connect(String url, String driver)");
		} catch (SQLException e) {
			System.out.println("[CMAT ERROR] - [QUERYHANDLER] - Could not connect to the database.");
		}
		return connection;
	}

	public Connection connect() {
		final String url = "jdbc:mysql://localhost:3306/student2011?user=student2011&password=parajan2011";
		final String driver = "com.mysql.jdbc.Driver";
		return connect(url, driver);
	}
	
	public void connectOldDB() {
		Connection oldCon = connect("jdbc:oracle:thin:COURSE/COURSE@143.169.244.40:1521:TEDIDB", "oracle.jdbc.driver.OracleDriver");
		if(oldCon != null) {
			this.fOldConnection = oldCon;
		}
	}

	/*
	 * Search function
	 */
	public Collection<Entity> search(String search) throws SQLException {
		Collection<Entity> c = new HashSet<Entity>();
		c.addAll(this.searchPrograms(search));
		c.addAll(this.searchCourses(search));
		c.addAll(this.searchSyllabuses(search));
		c.addAll(this.searchPackets(search));
		return c;
	}
	
	/*
	 * Search programs
	 */
	public Collection<Entity> searchPrograms(String search) throws SQLException {
		Collection<Entity> c = new HashSet<Entity>();
		final String query = "SELECT id, description, training_id FROM student2011.CMAT_PROGRAM  WHERE description LIKE '%" + search + "%' ORDER BY description DESC LIMIT 10 ";
		final PreparedStatement statement = fConnection.prepareStatement(query);
		final ResultSet resultSet = statement.executeQuery();
		while (resultSet.next()) {
			c.add(new Program(resultSet.getString("id"), resultSet.getString("description"), resultSet.getString("training_id")));
		}
		statement.close();
		resultSet.close();
		return c;
	}
	
	/*
	 * Search syllabuses
	 */
	public Collection<Entity> searchSyllabuses(String search) throws SQLException {
		Collection<Entity> c = new HashSet<Entity>();
		final String query = "SELECT id, description, nr, academic_year, price, available FROM student2011.CMAT_SYLLABUS WHERE (" +
				"description LIKE '%" + search + "%' " +
				"OR nr LIKE '%" + search + "%') " +
				"AND academic_year=" + Util.currentAcYear() + " " +
				"ORDER BY description ASC LIMIT 10";
		final PreparedStatement statement = fConnection.prepareStatement(query);
		final ResultSet resultSet = statement.executeQuery();
		while (resultSet.next()) {
			c.add(new Syllabus(resultSet.getString("id"), 
					resultSet.getDouble("price"), 
					resultSet.getString("description"), 
					resultSet.getString("academic_year"), 
					resultSet.getBoolean("available"), 
					resultSet.getInt("nr")));
		}
		statement.close();
		resultSet.close();
		return c;
	}
	
	/*
	 * Search packets
	 */
	
	public Collection<Entity> searchPackets(String search) throws SQLException {
		Collection<Entity> c = new HashSet<Entity>();
		final String query = "SELECT id, description, program_id, academic_year FROM student2011.CMAT_PACKET WHERE description LIKE '%" + search + "%' AND academic_year=" + Util.currentAcYear() + " ORDER BY description ASC LIMIT 10 ";
		final PreparedStatement statement = fConnection.prepareStatement(query);
		final ResultSet resultSet = statement.executeQuery();
		while (resultSet.next()) {
			c.add(new Packet(resultSet.getString("id"), resultSet.getString("description"), resultSet.getInt("academic_year"), resultSet.getString("program_id")));
		}
		statement.close();
		resultSet.close();
		return c;
	}
	
	/*
	 * Search courses
	 */
	public Collection<Entity> searchCourses(String search) throws SQLException {
		Collection<Entity> c = new HashSet<Entity>();
		final String query = "SELECT id, description FROM student2011.CMAT_COURSE WHERE description LIKE '%" + search + "%' ORDER BY description ASC LIMIT 10";
		final PreparedStatement statement = fConnection.prepareStatement(query);
		final ResultSet resultSet = statement.executeQuery();
		while (resultSet.next()) {
			c.add(new Course(resultSet.getString("id"), resultSet.getString("description")));
		}
		statement.close();
		resultSet.close();
		return c;
	}
	
	/*
	 * Get program for given id
	 */
	public Program getProgram(String id) throws SQLException {
		Program program = null;
        final String query = "SELECT description, training_id FROM student2011.CMAT_PROGRAM WHERE id='" + id + "' LIMIT 1";
        final PreparedStatement statement = fConnection.prepareStatement(query);
        final ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()) {
                program = new Program(id, resultSet.getString("description"), resultSet.getString("training_id"));

        }
        statement.close();
        resultSet.close();
        return program;
	}
	
	/*
	 * Get training for given id
	 */
	public Training getTraining(String id) throws SQLException {
		Training training = null;
        final String query = "SELECT description FROM student2011.CMAT_TRAINING WHERE id='" + id + "' LIMIT 1";
        final PreparedStatement statement = fConnection.prepareStatement(query);
        final ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()) {
                training = new Training(id, resultSet.getString("description"));

        }
        statement.close();
        resultSet.close();
        return training;
	}
	
	/*
	 * Get faculty for given id
	 */
	public Faculty getFaculty(String id) throws SQLException {
		Faculty fac = null;
        final String query = "SELECT m.name FROM student2011.MAP_DEPARTMENTS m WHERE m.id='" + id + "'";
        final PreparedStatement statement = fConnection.prepareStatement(query);
        final ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()) {
                fac = new Faculty(id, resultSet.getString("m.name"));
                break;
        }
        statement.close();
        resultSet.close();
        return fac;
	}
	
	public Syllabus getSyllabus(String id) throws SQLException {
		Syllabus syllabus = null;
        final String query = "SELECT description, nr, academic_year, price, available FROM student2011.CMAT_SYLLABUS WHERE id='" + id + "' LIMIT 1";
        final PreparedStatement statement = fConnection.prepareStatement(query);
        final ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()) {
        	System.out.println("price " + resultSet.getDouble("price"));
                syllabus = new Syllabus(id, resultSet.getDouble("price"), 
                		resultSet.getString("description"), 
                		resultSet.getString("academic_year"), 
                		resultSet.getBoolean("available"), 
                		resultSet.getInt("nr"));

        }
        statement.close();
        resultSet.close();
        return syllabus;
	}
	
	public Packet getPacket(String id) throws SQLException {
		Packet packet = null;
        final String query = "SELECT id, description, academic_year, program_id FROM student2011.CMAT_PACKET WHERE id='" + id + "' LIMIT 1";
        final PreparedStatement statement = fConnection.prepareStatement(query);
        final ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()) {
        	packet = new Packet(resultSet.getString("id"), resultSet.getString("description"), resultSet.getInt("academic_year"), resultSet.getString("program_id"));

        }
        statement.close();
        resultSet.close();
        return packet;
	}
	
	public Course getCourse(String id) throws SQLException {
		Course course = null;
        final String query = "SELECT description FROM student2011.CMAT_COURSE WHERE id='" + id + "' LIMIT 1";
        final PreparedStatement statement = fConnection.prepareStatement(query);
        final ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()) {
        	course = new Course(id, resultSet.getString("description"));
        }
        statement.close();
        resultSet.close();
        return course;
	}
	
	public Collection<Packet> getPacketsForProgram(String id) throws SQLException {
		Collection<Packet> packets = new Vector<Packet>();
		final String query = "SELECT id, description, academic_year, program_id FROM student2011.CMAT_PACKET where program_id = '" + id + "' AND academic_year=" + Util.currentAcYear();
		System.out.println("OUTPUTTEST: " + query);
		final PreparedStatement statement = fConnection.prepareStatement(query);
        final ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()) {
        	packets.add(new Packet(resultSet.getString("id"), resultSet.getString("description"), resultSet.getInt("academic_year"), resultSet.getString("program_id")));
        }
        statement.close();
        resultSet.close();
        return packets;
	}
	
	public Collection<Syllabus> getSyllabusesForPacket(String id) throws SQLException {
		Collection<Syllabus> syllabuses = new Vector<Syllabus>();
		final String query = "SELECT c.id, c.nr, c.academic_year, c.price, c.available, c.description " +
				"FROM student2011.CMAT_PACKET_SYLLABUS AS s INNER JOIN student2011.CMAT_SYLLABUS AS c ON c.id=s.syllabus_id " +
				"WHERE s.packet_id='" + id + "' ORDER BY c.description ASC";
		final PreparedStatement statement = fConnection.prepareStatement(query);
        final ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()) {
        	syllabuses.add(new Syllabus(resultSet.getString("c.id"),
        			resultSet.getDouble("c.price"),
        			resultSet.getString("c.description"),
        			resultSet.getString("academic_year"),
        			resultSet.getBoolean("c.available"),
        			resultSet.getInt("c.nr")
        			));
        }
        statement.close();
        resultSet.close();
        return syllabuses;
	}
	
	public Collection<Training> getTrainingsForFaculty(String id) throws SQLException {
		Collection<Training> trainings = new Vector<Training>();
		final String query = "SELECT t.id, t.description FROM CMAT_TRAINING t WHERE t.id IN (SELECT p.training_id FROM CMAT_PROGRAM p WHERE p.faculty_id='"+ id +"') ORDER BY t.description ASC";
		final PreparedStatement statement = fConnection.prepareStatement(query);
        final ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()) {
        	trainings.add(new Training(resultSet.getString("t.id"), resultSet.getString("t.description")));
        }
        statement.close();
        resultSet.close();
        return trainings;
	}
	
	public Collection<Program> getProgramsForTraining(String id) throws SQLException {
		Collection<Program> programs = new Vector<Program>();
		final String query = "SELECT p.id, p.description FROM CMAT_PROGRAM p WHERE p.training_id='"+ id +"' ORDER BY p.description ASC";
		final PreparedStatement statement = fConnection.prepareStatement(query);
        final ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()) {
        	programs.add(new Program(resultSet.getString("p.id"), resultSet.getString("p.description"), id));
        }
        statement.close();
        resultSet.close();
        return programs;
	}
	
	public Collection<String> getCampusesForSyllabus(String id) throws SQLException {
		Collection<String> campuses = new HashSet<String>();
		final String query = "SELECT campus_id FROM student2011.CMAT_SYLLABUS_CAMPUS WHERE syllabus_id='" + id + "'";
		final PreparedStatement statement = fConnection.prepareStatement(query);
        final ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()) {
        	campuses.add(resultSet.getString("campus_id"));
        }
        statement.close();
        resultSet.close();
        return campuses;
	}
	
	public Collection<String> getCampusesForPacket(String id) throws SQLException {
		Collection<String> campuses = new HashSet<String>();
		final String query = "SELECT campus_id FROM student2011.CMAT_PACKET_CAMPUS WHERE packet_id='" + id + "'";
		final PreparedStatement statement = fConnection.prepareStatement(query);
        final ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()) {
        	campuses.add(resultSet.getString("campus_id"));
        }
        statement.close();
        resultSet.close();
        return campuses;
	}
	
	public Collection<Course> getCoursesForSyllabus(String id) throws SQLException {
		Collection<Course> courses = new Vector<Course>();
		final String query = "SELECT c.id, c.description " +
				"FROM student2011.CMAT_COURSE_SYLLABUS AS s INNER JOIN student2011.CMAT_COURSE AS c ON c.id=s.course_id " +
				"WHERE s.syllabus_id='" + id + "' ORDER BY c.description ASC";
		final PreparedStatement statement = fConnection.prepareStatement(query);
        final ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()) {
        	courses.add(new Course(resultSet.getString("c.id"), resultSet.getString("c.description")));
        }
        statement.close();
        resultSet.close();
        return courses;
	}
	
	public Collection<Course> getCoursesForProgram(String id) throws SQLException {
		Collection<Course> courses = new Vector<Course>();
		final String query = "SELECT c.id, c.description " +
				"FROM student2011.CMAT_COURSE_PROGRAM AS s INNER JOIN student2011.CMAT_COURSE AS c ON c.id=s.course_id " +
				"WHERE s.program_id='" + id + "' ORDER BY c.description ASC";
		final PreparedStatement statement = fConnection.prepareStatement(query);
        final ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()) {
        	courses.add(new Course(resultSet.getString("c.id"), resultSet.getString("c.description")));
        }
        statement.close();
        resultSet.close();
        return courses;
	}
	
	public Collection<Syllabus> getSyllabusesForCourse(String id) throws SQLException {
		Collection<Syllabus> syllabuses = new Vector<Syllabus>();
		final String query = "SELECT c.id, c.nr, c.academic_year, c.price, c.available, c.description " +
				"FROM student2011.CMAT_COURSE_SYLLABUS AS s INNER JOIN student2011.CMAT_SYLLABUS AS c ON c.id=s.syllabus_id " +
				"WHERE s.course_id='" + id + "' AND c.academic_year=" + Util.currentAcYear() + " ORDER BY c.description ASC";
		final PreparedStatement statement = fConnection.prepareStatement(query);
        final ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()) {
        	syllabuses.add(new Syllabus(resultSet.getString("c.id"),
        			resultSet.getDouble("c.price"),
        			resultSet.getString("c.description"),
        			resultSet.getString("academic_year"),
        			resultSet.getBoolean("c.available"),
        			resultSet.getInt("c.nr")
        			));
        }
        statement.close();
        resultSet.close();
        return syllabuses;
	}
	
	public Collection<Program> getProgramsForCourse(String id) throws SQLException {
		Collection<Program> programs = new Vector<Program>();
		final String query = "SELECT c.id, c.description, c.training_id " +
				"FROM student2011.CMAT_COURSE_PROGRAM AS s INNER JOIN student2011.CMAT_PROGRAM AS c ON c.id=s.program_id " +
				"WHERE s.course_id='" + id + "' ORDER BY c.description ASC";
		final PreparedStatement statement = fConnection.prepareStatement(query);
        final ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()) {
        	programs.add(new Program(resultSet.getString("c.id"), resultSet.getString("c.description"), resultSet.getString("c.training_id")));
        }
        statement.close();
        resultSet.close();
        return programs;
	}
	
	public Collection<Faculty> getFaculties() throws SQLException {
		Collection<Faculty> faculties = new Vector<Faculty>();
		
		final String query = "SELECT m.id, m.name FROM MAP_DEPARTMENTS m WHERE m.id IN (SELECT DISTINCT(p.faculty_id) FROM CMAT_PROGRAM p) ORDER BY m.name ASC";
		final PreparedStatement statement = fConnection.prepareStatement(query);
        final ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()) {
        	faculties.add(new Faculty(resultSet.getString("m.id"), resultSet.getString("m.name")));
        }
        statement.close();
        resultSet.close();
		return faculties;
	}
	
	public boolean synchTrainings() {
		String select;
		String insert;
		String check;
		ResultSet result;
		
		try {
			//Add new records
			select = "SELECT * FROM CUR.CUR_OPLEIDING";
			result = fOldConnection.createStatement().executeQuery(select);
			String descr = "";
			String opl_id = "";

			while (result.next()) {
				descr = result.getString("DESCR");
				opl_id = result.getString("OPL_ID");
				
				check = "SELECT * FROM student2011.CMAT_TRAINING WHERE description=? AND prev_key=?";
				PreparedStatement statement = fConnection.prepareStatement(check);
				statement.setString(1, descr);
				statement.setString(2, opl_id);
				ResultSet resultSet = statement.executeQuery();
				if(!resultSet.next()) {
					insert = "INSERT INTO student2011.CMAT_TRAINING (description, prev_key) VALUES ('" + descr + "', '" + opl_id + "')";
					fConnection.createStatement().executeUpdate(insert);
				}
				statement.close();
			}
			
			//Remove old records
			select = "SELECT * FROM student2011.CMAT_TRAINING";
			result = fConnection.createStatement().executeQuery(select);
			descr = "";
			opl_id = "";

			while (result.next()) {
				int id = result.getInt("id");
				descr = result.getString("description");
				opl_id = result.getString("prev_key");
				
				check = "SELECT * FROM CUR.CUR_OPLEIDING WHERE DESCR=? AND OPL_ID=?";
				PreparedStatement statement = fOldConnection.prepareStatement(check);
				statement.setString(1, descr);
				statement.setString(2, opl_id);
				ResultSet resultSet = statement.executeQuery();
				if(!resultSet.next()) {
					insert = "DELETE FROM student2011.CMAT_TRAINING WHERE id=?";
					PreparedStatement insertStatement = fConnection.prepareStatement(insert);
					insertStatement.setInt(1, id);
					fConnection.createStatement().executeUpdate(insert);
				}
				statement.close();
			}
			
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
	
		return true;
	}
	
	public boolean synchCourses() {
		String select;
		String insert;
		String check;
		ResultSet result;
		PreparedStatement insertStatement;
		
		try {
			//Add new records
			select = "SELECT * FROM CUR.CUR_VAK WHERE USERMANTAINED=0";
			result = fOldConnection.createStatement().executeQuery(select);
			
			String descr = "";
			String vak_id = "";
	
			while (result.next()) {
				descr = result.getString("DESCR");
				vak_id = result.getString("VAK_ID");
				
				check = "SELECT * FROM student2011.CMAT_COURSE WHERE description=? AND prev_key=?";
				PreparedStatement statement = fConnection.prepareStatement(check);
				statement.setString(1, descr);
				statement.setString(2, vak_id);
				ResultSet resultSet = statement.executeQuery();
				if(!resultSet.next()) {
					insert = "INSERT INTO student2011.CMAT_COURSE (description, prev_key) VALUES (?, ?)";
					insertStatement = fConnection.prepareStatement(insert);
					insertStatement.setString(1, descr);
					insertStatement.setString(2, vak_id);
					insertStatement.executeUpdate();
					insertStatement.close();
				}
				statement.close();
			}
			
			//Remove old records
			select = "SELECT * FROM student2011.CMAT_COURSE";
			result = fConnection.createStatement().executeQuery(select);
			
			descr = "";
			vak_id = "";
	
			while (result.next()) {
				int id = result.getInt("id");
				descr = result.getString("description");
				vak_id = result.getString("prev_key");
				
				check = "SELECT * FROM CUR.CUR_VAK WHERE DESCR=? AND VAK_ID=?";
				PreparedStatement statement = fOldConnection.prepareStatement(check);
				statement.setString(1, descr);
				statement.setString(2, vak_id);
				ResultSet resultSet = statement.executeQuery();
				if(!resultSet.next()) {
					insert = "DELETE FROM student2011.CMAT_COURSE WHERE id=?";
					insertStatement = fConnection.prepareStatement(insert);
					insertStatement.setInt(1, id);
					insertStatement.executeUpdate();
					insertStatement.close();
				}
				statement.close();
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
	
		return true;
	}
	
	//Synchs both CMAT_SYLLABUS AND CMAT_SYLLABUS_CAMPUS
	public boolean synchSyllabus() {
		String select;
		String insert;
		String check;
		ResultSet result;
		PreparedStatement insertStatement;
		
		try {
			//Add new records
			select = "SELECT * FROM CUR.CUR_CURSUS WHERE WEB=1";
			result = fOldConnection.createStatement().executeQuery(select);
			
			int cur_nr;
			int cur_ac_jr;
			int campus;
			String omschr = "";
			float verkoop_prijs;

			while (result.next()) {
				cur_nr = result.getInt("CUR_NR");
				cur_ac_jr = result.getInt("CUR_AC_JR");
				campus = result.getInt("CAMPUS");
				omschr = result.getString("OMSCHR");
				verkoop_prijs = result.getFloat("VERKOOP_PRIJS");
				
				check = "SELECT * FROM student2011.CMAT_SYLLABUS WHERE nr=? AND academic_year=? AND old_campus=?";
				PreparedStatement statement = fConnection.prepareStatement(check);
				statement.setInt(1, cur_nr);
				statement.setInt(2, cur_ac_jr);
				statement.setInt(3, campus);
				ResultSet resultSet = statement.executeQuery();
				if(!resultSet.next()) {
					//CMAT_SYLLABUS
					insert = "INSERT INTO student2011.CMAT_SYLLABUS (nr, academic_year, old_campus, description, price) VALUES (?, ?, ?, ?, ?)";
					insertStatement = fConnection.prepareStatement(insert);
					insertStatement.setInt(1, cur_nr);
					insertStatement.setInt(2, cur_ac_jr);
					insertStatement.setInt(3, campus);
					insertStatement.setString(4, omschr);
					insertStatement.setFloat(5, verkoop_prijs);
					insertStatement.executeUpdate();
					insertStatement.close();
				}
				statement.close();
				resultSet.close();
			}
			result.close();
			
			//Add new SYLLABUS_CAMPUS records
			select = "SELECT * FROM student2011.CMAT_SYLLABUS";
			PreparedStatement statement = fConnection.prepareStatement(select);
			result = statement.executeQuery();
			while(result.next()) {
				int key = result.getInt("id");
				int nr = result.getInt("nr");
				String newCampus;
				if(result.getInt("old_campus") == 3) {
					newCampus = "CDE";
				} else {
					newCampus = "CGB";
				}
				insert = "INSERT IGNORE INTO student2011.CMAT_SYLLABUS_CAMPUS (campus_id, syllabus_id, available, syllabus_nr) VALUES (?, ?, ?, ?)";
				insertStatement = fConnection.prepareStatement(insert);
				insertStatement.setString(1, newCampus);
				insertStatement.setInt(2, key);
				insertStatement.setBoolean(3, true);
				insertStatement.setInt(4, nr);
				insertStatement.executeUpdate();
				insertStatement.close();
			}
			statement.close();
			result.close();
			
			//Remove old records
			select = "SELECT * FROM student2011.CMAT_SYLLABUS";
			result = fConnection.createStatement().executeQuery(select);
			
			while (result.next()) {
				cur_nr = result.getInt("nr");
				cur_ac_jr = result.getInt("academic_year");
				campus = result.getInt("old_campus");
				
				check = "SELECT * FROM CUR.CUR_CURSUS WHERE WEB=1 AND CUR_NR=? AND CUR_AC_JR=? AND CAMPUS=?";
				statement = fOldConnection.prepareStatement(check);
				statement.setInt(1, cur_nr);
				statement.setInt(2, cur_ac_jr);
				statement.setInt(3, campus);
				ResultSet resultSet = statement.executeQuery();
				if(!resultSet.next()) {
					//CMAT_SYLLABUS
					insert = "DELETE FROM student2011.CMAT_SYLLABUS WHERE nr=? AND academic_year=? AND old_campus=?";
					insertStatement = fConnection.prepareStatement(insert);
					insertStatement.setInt(1, cur_nr);
					insertStatement.setInt(2, cur_ac_jr);
					insertStatement.setInt(3, campus);
					insertStatement.executeUpdate();
					insertStatement.close();
				}
				statement.close();
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
		return true;
	}
	
	public boolean synchPrograms() {
		String select;
		String insert;
		String check;
		ResultSet result;
		PreparedStatement insertStatement;

		try {
			//Add new records
			select = "SELECT * FROM CUR.CUR_PROGRAMMA";
			result = fOldConnection.createStatement().executeQuery(select);
			
			String old_key;
			String old_fac;
			String faculty_id = "";
			String old_training;
			int training_id = 0;
			String descr;
	
			while (result.next()) {
				old_key = result.getString("PROGRAMMA_ID");
				old_fac = result.getString("FAC_ID");
				old_training = result.getString("OPL_ID");
				descr = result.getString("DESCR");
				
				//Select new faculty key and training id
				String facSelect = "SELECT * FROM CUR.CUR_FACULTEIT WHERE FAC_ID=?";
				PreparedStatement statement = fOldConnection.prepareStatement(facSelect);
				statement.setString(1, old_fac);
				ResultSet res = statement.executeQuery();
				while(res.next()) {
					faculty_id = res.getString("DEPT_ID");
				}
				statement.close();
				
				res = fConnection.createStatement().executeQuery("SELECT id FROM CMAT_TRAINING WHERE prev_key=\""+ old_training +"\"");
				while(res.next()) {
					training_id = res.getInt("id");
				}
				
				check = "SELECT * FROM student2011.CMAT_PROGRAM WHERE faculty_id=? AND training_id=?";
				statement = fConnection.prepareStatement(check);
				statement.setString(1, faculty_id);
				statement.setInt(2, training_id);
				res = statement.executeQuery();
				if(!res.next()) {
					//Insert data
					insert = "INSERT INTO student2011.CMAT_PROGRAM (faculty_id, training_id, description, prev_key) VALUES (?, ?, ?, ?)";
					insertStatement = fConnection.prepareStatement(insert);
					insertStatement.setString(1, faculty_id);
					insertStatement.setInt(2, training_id);
					insertStatement.setString(3, descr);
					insertStatement.setString(4, old_key);
					insertStatement.executeUpdate();
					insertStatement.close();
				}
				statement.close();
			}
			
			//Remove old records
			select = "SELECT * FROM student2011.CMAT_PROGRAM";
			result = fConnection.createStatement().executeQuery(select);
			
			while (result.next()) {
				int id = result.getInt("id");
				old_key = result.getString("prev_key");
				
				//Select new faculty key and training id
				check = "SELECT * FROM CUR.CUR_PROGRAMMA WHERE PROGRAMMA_ID=?";
				PreparedStatement statement = fOldConnection.prepareStatement(check);
				statement.setString(1, old_key);
				ResultSet res = statement.executeQuery();
				if(!res.next()) {
					//Remove data
					insert = "DELETE FROM student2011.CMAT_PROGRAM WHERE id=?";
					insertStatement = fConnection.prepareStatement(insert);
					insertStatement.setInt(1, id);
					insertStatement.executeUpdate();
					insertStatement.close();
				}
				statement.close();
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
		
		return true;
	}
	
	//Synchs CMAT_PACKET and CMAT_PACKET_CAMPUS
	public boolean synchPackets() {
		String select;
		String insert;
		String check;
		ResultSet result;
		PreparedStatement insertStatement;
		
		try {
			//Add new records
			select = "SELECT * FROM CUR.CUR_PAKKET";
			result = fOldConnection.createStatement().executeQuery(select);
			
			int pkt_nr;
			int ac_jr;
			int campus;
			String programma_id;
			String descr;
			
			while (result.next()) {
				pkt_nr = result.getInt("PKT_NR");
				ac_jr = result.getInt("AC_JR");
				campus = result.getInt("CAMPUS");
				programma_id = result.getString("PROGRAMMA_ID");
				descr = result.getString("DESCR");
				int new_program_id = -1;
				
				check = "SELECT * FROM student2011.CMAT_PACKET WHERE nr=? AND academic_year=? AND campus_id=?";
				PreparedStatement statement = fConnection.prepareStatement(check);
				statement.setInt(1, pkt_nr);
				statement.setInt(2, ac_jr);
				statement.setInt(3, campus);
				ResultSet res = statement.executeQuery();
				if(!res.next()) {
					//Select new program id
					res = fConnection.createStatement().executeQuery("SELECT id FROM CMAT_PROGRAM WHERE prev_key=\""+ programma_id +"\"");
					while(res.next()) {
						new_program_id = res.getInt("id");
					}
					
					if(new_program_id >= 0) {
						//CMAT_PACKET
						insert = "INSERT INTO student2011.CMAT_PACKET (nr, academic_year, campus_id, program_id, description) VALUES (?, ?, ?, ?, ?)";
						insertStatement = fConnection.prepareStatement(insert);
						insertStatement.setInt(1, pkt_nr);
						insertStatement.setInt(2, ac_jr);
						insertStatement.setInt(3, campus);
						insertStatement.setInt(4, new_program_id);
						insertStatement.setString(5, descr);
						insertStatement.executeUpdate();
						insertStatement.close();
					}
				}
				statement.close();
				res.close();
			}
			result.close();
			
			//CMAT_PACKET_CAMPUS
			
			select = "SELECT * FROM student2011.CMAT_PACKET";
			PreparedStatement statement = fConnection.prepareStatement(select);
			ResultSet res = statement.executeQuery();
			while(res.next()) {
				int key = res.getInt("id");
				String newCampus;
				if(res.getInt("campus_id") == 3) {
					newCampus = "CDE";
				} else {
					newCampus = "CGB";
				}
				int nr = res.getInt("nr");
				insert = "INSERT IGNORE INTO student2011.CMAT_PACKET_CAMPUS (packet_id, campus_id, packet_nr) VALUES (?, ?, ?)";
				insertStatement = fConnection.prepareStatement(insert);
				insertStatement.setInt(1, key);
				insertStatement.setString(2, newCampus);
				insertStatement.setInt(3, nr);
				insertStatement.executeUpdate();
				insertStatement.close();
			}
			statement.close();
			res.close();
			
			//Remove old records
			select = "SELECT * FROM student2011.CMAT_PACKET";
			result = fConnection.createStatement().executeQuery(select);
			
			while (result.next()) {
				pkt_nr = result.getInt("nr");
				ac_jr = result.getInt("academic_year");
				campus = result.getInt("campus_id");
				int id = result.getInt("id");
				
				check = "SELECT * FROM CUR.CUR_PAKKET WHERE PKT_NR=? AND AC_JR=? AND CAMPUS=?";
				statement = fOldConnection.prepareStatement(check);
				statement.setInt(1, pkt_nr);
				statement.setInt(2, ac_jr);
				statement.setInt(3, campus);
				res = statement.executeQuery();
				if(!res.next()) {
					//Select new program id
					insert = "DELETE FROM student2011.CMAT_PACKET WHERE id=?";
					insertStatement = fConnection.prepareStatement(insert);
					insertStatement.setInt(1, id);
					insertStatement.executeUpdate();
					insertStatement.close();
				}
				statement.close();
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
		
		return true;
	}
	
	public boolean synchPacketSyllabus() {
		String select;
		String insert;
		String check;
		ResultSet result;
		PreparedStatement insertStatement;
		
		try {
			//Insert new records
			select = "SELECT * FROM CUR.CUR_PAKKET_CURSUS";
			result = fOldConnection.createStatement().executeQuery(select);
			
			int pkt_nr = -1;
			int pkt_ac_jr = -1;
			int cur_nr = -1;
			int cur_ac_jr = -1;
			int campus = -1;
			
			while (result.next()) {
				pkt_nr = result.getInt("PKT_NR");
				pkt_ac_jr = result.getInt("PKT_AC_JR");
				cur_nr = result.getInt("CUR_NR");
				cur_ac_jr = result.getInt("CUR_AC_JR");
				campus = result.getInt("CAMPUS");
				int new_packet_id = -1;
				int new_syllabus_id = -1;

				//Select new packet id
				ResultSet res = fConnection.createStatement().executeQuery("SELECT id FROM CMAT_PACKET WHERE nr="+pkt_nr+" AND academic_year="+pkt_ac_jr+" AND campus_id="+campus);
				while(res.next()) {
					new_packet_id = res.getInt("id");
				}
				res.close();
				
				//Select new syllabus id
				res = fConnection.createStatement().executeQuery("SELECT id FROM CMAT_SYLLABUS WHERE nr="+cur_nr+" AND academic_year="+cur_ac_jr+" AND old_campus="+campus);
				while(res.next()) {
					new_syllabus_id = res.getInt("id");
				}
				res.close();
				
				//Insert data
				if((new_packet_id >= 0) && (new_syllabus_id >= 0)) {
					insert = "INSERT IGNORE INTO student2011.CMAT_PACKET_SYLLABUS (packet_id, syllabus_id) VALUES (?, ?)";
					insertStatement = fConnection.prepareStatement(insert);
					insertStatement.setInt(1, new_packet_id);
					insertStatement.setInt(2, new_syllabus_id);
					insertStatement.executeUpdate();
					insertStatement.close();
				}
			}
			
			//Remove old records
			select = "SELECT * FROM student2011.CMAT_PACKET_SYLLABUS";
			result = fConnection.createStatement().executeQuery(select);
			
			while (result.next()) {
				int new_packet_id = result.getInt("packet_id");
				int new_syllabus_id = result.getInt("syllabus_id");

				//Select old packet data
				String selectPacket = "SELECT * FROM CMAT_PACKET WHERE id=?";
				PreparedStatement packetStatement = fConnection.prepareStatement(selectPacket);
				packetStatement.setInt(1, new_packet_id);
				ResultSet res = packetStatement.executeQuery();
				while(res.next()) {
					pkt_nr = res.getInt("nr");
					pkt_ac_jr = res.getInt("academic_year");
					campus = res.getInt("campus_id");
				}
				packetStatement.close();
				
				//Select old syllabus data
				String selectSyllabus = "SELECT * FROM CMAT_SYLLABUS WHERE id=?";
				PreparedStatement syllabusStatement = fConnection.prepareStatement(selectSyllabus);
				syllabusStatement.setInt(1, new_syllabus_id);
				res = syllabusStatement.executeQuery();
				while(res.next()) {
					cur_nr = res.getInt("nr");
					cur_ac_jr = res.getInt("academic_year");
				}
				syllabusStatement.close();
				
				check = "SELECT * FROM CUR.CUR_PAKKET_CURSUS WHERE PKT_NR=? AND PKT_AC_JR=? AND CAMPUS=? AND CUR_NR=? AND CUR_AC_JR=?";
				PreparedStatement checkStatement = fOldConnection.prepareStatement(check);
				checkStatement.setInt(1, pkt_nr);
				checkStatement.setInt(2, pkt_ac_jr);
				checkStatement.setInt(3, campus);
				checkStatement.setInt(4, cur_nr);
				checkStatement.setInt(5, cur_ac_jr);
				ResultSet checkResult = checkStatement.executeQuery();
				if(!checkResult.next()) {
					insert = "DELETE FROM CMAT_PACKET_SYLLABUS WHERE packet_id=? AND syllabus_id=?";
					insertStatement = fConnection.prepareStatement(insert);
					insertStatement.setInt(1, new_packet_id);
					insertStatement.setInt(2, new_syllabus_id);
					insertStatement.executeUpdate();
					insertStatement.close();
				}
				checkStatement.close();
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
		
		return true;
	}
	
	public boolean synchCourseProgram() {
		String select;
		String insert;
		String check;
		ResultSet result;
		PreparedStatement insertStatement;
		
		try {
			//Insert new records
			select = "SELECT * FROM CUR.CUR_VAK_PROGRAMMA";
			result = fOldConnection.createStatement().executeQuery(select);
			
			String programma_id;
			String vak_id;
			
			while (result.next()) {
				programma_id = result.getString("PROGRAMMA_ID");
				vak_id = result.getString("VAK_ID");
				int new_program_id = -1;
				int new_course_id = -1;
						
				//Select new program id
				ResultSet res = fConnection.createStatement().executeQuery("SELECT id FROM CMAT_PROGRAM WHERE prev_key=\""+ programma_id +"\"");
				while(res.next()) {
					new_program_id = res.getInt("id");
				}
				
				//Select new course id
				res = fConnection.createStatement().executeQuery("SELECT id FROM CMAT_COURSE WHERE prev_key=\""+ vak_id +"\"");
				while(res.next()) {
					new_course_id = res.getInt("id");
				}
				
				//Insert data
				if((new_program_id >= 0) && (new_course_id >= 0)) {
					insert = "INSERT IGNORE INTO student2011.CMAT_COURSE_PROGRAM (course_id, program_id) VALUES (?, ?)";
					insertStatement = fConnection.prepareStatement(insert);
					insertStatement.setInt(1, new_course_id);
					insertStatement.setInt(2, new_program_id);
				
					insertStatement.executeUpdate();
					insertStatement.close();
				}
			}
			
			//Remove old records
			select = "SELECT * FROM student2011.CMAT_COURSE_PROGRAM";
			result = fConnection.createStatement().executeQuery(select);
			
			while (result.next()) {
				int new_program_id = result.getInt("program_id");
				int new_course_id = result.getInt("course_id");
						
				vak_id = "";
				programma_id = "";
				
				//Select old program id
				String programSelect = "SELECT * FROM student2011.CMAT_PROGRAM WHERE id=?";
				PreparedStatement programStatement = fConnection.prepareStatement(programSelect);
				programStatement.setInt(1, new_program_id);
				ResultSet res = programStatement.executeQuery();
				while(res.next()) {
					programma_id = res.getString("prev_key");
				}
				programStatement.close();
				
				//Select old course id
				String courseSelect = "SELECT * FROM student2011.CMAT_COURSE WHERE id=?";
				PreparedStatement courseStatement = fConnection.prepareStatement(courseSelect);
				courseStatement.setInt(1, new_course_id);
				res = courseStatement.executeQuery();
				while(res.next()) {
					vak_id = res.getString("prev_key");
				}
				courseStatement.close();
				
				check = "SELECT * FROM CUR.CUR_VAK_PROGRAMMA WHERE VAK_ID=? AND PROGRAMMA_ID=?";
				PreparedStatement checkStatement = fOldConnection.prepareStatement(check);
				checkStatement.setString(1, vak_id);
				checkStatement.setString(2, programma_id);
				res = checkStatement.executeQuery();
				if(!res.next()) {
					insert = "DELETE FROM student2011.CMAT_COURSE_PROGRAM WHERE program_id=? AND course_id=?";
					insertStatement = fConnection.prepareStatement(insert);
					insertStatement.setInt(1, new_program_id);
					insertStatement.setInt(2, new_course_id);
					insertStatement.executeUpdate();
					insertStatement.close();
				}
				checkStatement.close();
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
		
		return true;
	}
	
	public boolean synchCourseSyllabus() {
		String select;
		String insert;
		String check;
		ResultSet result;
		PreparedStatement insertStatement;
		
		try {
			//Insert new records
			select = "SELECT * FROM CUR.CUR_CURSUS_VAK_PROGRAMMA";
			result = fOldConnection.createStatement().executeQuery(select);
			
			int nr;
			int ac_jr;
			int campus;
			String vak_id;

			while (result.next()) {
				vak_id = result.getString("VAK_ID");
				nr = result.getInt("CUR_NR");
				ac_jr = result.getInt("CUR_AC_JR");
				campus = result.getInt("CAMPUS");
				
				int syllabus_id = -1;
				int course_id = -1;
						
				//Select new syllabus id
				ResultSet res = fConnection.createStatement().executeQuery("SELECT id FROM CMAT_SYLLABUS WHERE nr="+ nr +" AND academic_year="+ ac_jr +" AND old_campus="+campus);
				while(res.next()) {
					syllabus_id = res.getInt("id");
				}
				
				//Select new course id
				res = fConnection.createStatement().executeQuery("SELECT id FROM CMAT_COURSE WHERE prev_key=\""+ vak_id +"\"");
				while(res.next()) {
					course_id = res.getInt("id");
				}
				
				//Insert data
				if((syllabus_id >= 0) && (course_id >= 0)) {
					insert = "INSERT IGNORE INTO student2011.CMAT_COURSE_SYLLABUS (course_id, syllabus_id) VALUES (?, ?)";
					insertStatement = fConnection.prepareStatement(insert);
					insertStatement.setInt(1, course_id);
					insertStatement.setInt(2, syllabus_id);
				
					insertStatement.executeUpdate();
					insertStatement.close();
				}
			}
			
			//Remove old records
			select = "SELECT * FROM student2011.CMAT_COURSE_SYLLABUS";
			result = fConnection.createStatement().executeQuery(select);

			while (result.next()) {
				int new_course_id = result.getInt("course_id");
				int new_syllabus_id = result.getInt("syllabus_id");
				
				nr = -1;
				ac_jr = -1;
				campus = -1;
				vak_id = "";
				
				//Select old course id
				String selectCourse = "SELECT * FROM CMAT_COURSE WHERE id=?";
				PreparedStatement courseStatement = fConnection.prepareStatement(selectCourse);
				courseStatement.setInt(1, new_course_id);
				ResultSet res = courseStatement.executeQuery();
				while(res.next()) {
					vak_id = res.getString("prev_key");
				}
				courseStatement.close();
				
				//Select old syllabus data
				String selectSyllabus = "SELECT * FROM student2011.CMAT_SYLLABUS WHERE id=?";
				PreparedStatement syllabusStatement = fConnection.prepareStatement(selectSyllabus);
				syllabusStatement.setInt(1, new_syllabus_id);
				res = syllabusStatement.executeQuery();
				while(res.next()) {
					nr = res.getInt("nr");
					campus = res.getInt("old_campus");
					ac_jr = res.getInt("academic_year");
				}
				syllabusStatement.close();
				
				check = "SELECT * FROM CUR.CUR_CURSUS_VAK_PROGRAMMA WHERE CUR_NR=? AND CUR_AC_JR=? AND CAMPUS=? AND VAK_ID=?";
				PreparedStatement checkStatement = fOldConnection.prepareStatement(check);
				checkStatement.setInt(1, nr);
				checkStatement.setInt(2, ac_jr);
				checkStatement.setInt(3, campus);
				checkStatement.setString(4, vak_id);
				res = checkStatement.executeQuery();
				if(!res.next()) {
					insert = "DELETE FROM student2011.CMAT_COURSE_SYLLABUS WHERE course_id=? AND syllabus_id=?";
					insertStatement = fConnection.prepareStatement(insert);
					insertStatement.setInt(1, new_course_id);
					insertStatement.setInt(2, new_syllabus_id);
					insertStatement.executeUpdate();
					insertStatement.close();
				}
				checkStatement.close();
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
		
		return true;
	}
}